/*DONE* Table [dbo].[<CompanyName>$LSC Trans_ Infocode Entry$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 17.11.2022 - GKO ******/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$LSC Trans_ Infocode Entry$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$LSC Trans_ Infocode Entry$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[POS Terminal No_] [nvarchar](10) NOT NULL,
	[Transaction No_] [int] NOT NULL,
	[Transaction Type] [int] NOT NULL,
	[Line No_] [int] NOT NULL,
	[Infocode] [nvarchar](20) NOT NULL,
	[Entry Line No_] [int] NOT NULL,
	[Information] [nvarchar](100) NOT NULL,
	[Info_ Amt_] [decimal](38, 20) NOT NULL,
	[Text Type] [int] NOT NULL,
	[Date] [datetime] NOT NULL,
	[Time] [datetime] NOT NULL,
	[Staff ID] [nvarchar](20) NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Variant Code] [nvarchar](10) NOT NULL,
	[Amount] [decimal](38, 20) NOT NULL,
	[Type of Input] [int] NOT NULL,
	[Subcode] [nvarchar](20) NOT NULL,
	[Entry Variant Code] [nvarchar](10) NOT NULL,
	[Entry Trigger Function] [int] NOT NULL,
	[Entry Trigger Code] [nvarchar](20) NOT NULL,
	[Statement No_] [nvarchar](20) NOT NULL,
	[Statement Code] [nvarchar](20) NOT NULL,
	[Source Code] [nvarchar](20) NOT NULL,
	[Serial No_] [nvarchar](50) NOT NULL,
	[Selected Quantity] [decimal](38, 20) NOT NULL,
	[Counter] [decimal](38, 20) NOT NULL,
	[Replicated] [tinyint] NOT NULL,
	[Replication Counter] [int] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
	[Parent Line] [int] NOT NULL,
	[ParentLine] [int] NOT NULL,
 CONSTRAINT ['+@Company+'$LSC Trans_ Infocode Entry$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Store No_] ASC,
	[POS Terminal No_] ASC,
	[Transaction No_] ASC,
	[Transaction Type] ASC,
	[Line No_] ASC,
	[Infocode] ASC,
	[Entry Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$LSC Trans_ Infocode Entry$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i+1
END